import sqlite3
import csv
import os

# 数据库文件路径
# DATABASE = 'carbon_history.db'
DATABASE = os.path.join(os.path.dirname(os.path.abspath(__file__)),"carbon_history.db")

# 初始化数据库
def init_db():
    with sqlite3.connect(DATABASE) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS carbon_history (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                time TEXT NOT NULL,
                process_emission REAL NOT NULL,
                energy_consumption REAL NOT NULL,
                resource_consumption REAL NOT NULL,
                process_factor REAL NOT NULL,
                energy_factor REAL NOT NULL,
                resource_factor REAL NOT NULL,
                total_carbon REAL NOT NULL
            );
        ''')
        conn.commit()

# 添加历史记录
def add_history_record(data):
    with sqlite3.connect(DATABASE) as conn:
        cursor = conn.cursor()
        cursor.execute('''
            INSERT INTO carbon_history (time, process_emission, energy_consumption, resource_consumption, process_factor, energy_factor, resource_factor, total_carbon)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?);
        ''', data)
        conn.commit()

# 获取所有历史记录
def get_all_history_records():
    with sqlite3.connect(DATABASE) as conn:
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM carbon_history ORDER BY id DESC;')
        return cursor.fetchall()

# 清除所有历史记录
def clear_history_records():
    with sqlite3.connect(DATABASE) as conn:
        cursor = conn.cursor()
        cursor.execute('DELETE FROM carbon_history;')
        conn.commit()

# 导出历史记录为 CSV 文件
def export_history_to_csv(filename):
    with sqlite3.connect(DATABASE) as conn:
        cursor = conn.cursor()
        cursor.execute('SELECT * FROM carbon_history ORDER BY id DESC;')
        records = cursor.fetchall()

    with open(filename, 'w', newline='', encoding='utf-8') as file:
        writer = csv.writer(file)
        writer.writerow(['时间', '工艺排放 (吨)', '能源消耗 (吨)', '资源消耗 (吨)', '工艺排放因子', '能源消耗因子', '资源消耗因子', '总碳排放量 (吨)'])
        writer.writerows(records)